关于SQL我之前已经陆续更新了很多期，从入门到基础知识再到练习和面试技巧都有分享，大家感兴趣的话可以去看下这篇文章。

戎易大数据：几万字汇总了SQL全部知识点，必看！（含教程、面试技巧）

近几期主要围绕SQL进阶技巧来进行分享。

本篇主题为外连接的用法。

很多人对SQL有一个误解：它是一种用于生成报表的语言。确实，SQL在生成各种定制化或非定制化报表或统计表的系统里有着广泛的应用。这本身并没有什么问题，但“不幸”的是，数据库工程师开始要求SQL具备并非它原本用途的功能——格式转换。说起来，SQL终究也只是主要用于查询数据的语言而已。

但是同时，SQL比很多人想象得更加强大。特别是近些年，SQL引入了许多便于生成报表的功能，其中的代表就是窗口函数。如果SQL既可以简化系统整体的代码，同时也可以优化性能，使用起来是很有价值的。

本篇文章，我们将学习一下使用外连接进行格式转换的方法。外连接是数据库工程师比较熟悉的一种运算，但这次我们将试着从不同的角度来体会一下它的特性。就内容分布来说，本文前半部分主要讲解如何使用外连接进行格式转换，后半部分则从集合运算的角度来了解外连接。

如果大家对外连接完全不了解，可以先从后半部分的“全外连接”和“用外连接进行集合运算”两部分开始阅读。

在CASE表达式一文中，我们学习了将查询结果转换成交叉表的方法。这次，我们来思考一下如何用外连接的方法实现同样的功能。例如，这里有一张用于管理员工学习过的培训课程的表，如下所示。

Courses

首先让我们利用上面这张表生成下面这样一张交叉表（“课程学习记录一览表”）。○表示已学习过，NULL表示尚未学习。

课程学习记录一览表（表头：课程；侧栏：员工姓名）

实际上，原来的表与刚刚生成的表在信息量上并没有区别。关于“谁学习过哪些课程”，不管从哪张表都能看出来，区别只是外观。

所以，这本来并不是SQL应该做的工作。但是，练习用SQL进行这样的工作是本篇文章的主旨，所以我们尝试用外连接的思路来思考，这样就可以知道，以侧栏（员工姓名）为主表进行外连接操作就可以生成表。

使用子查询，根据源表Courses生成C0～C3这4个子集。自连接文章中也讲过，SQL中指定了名称的表和视图都相当于集合。因此，这里将生成下面这样4个集合。C0：主表

C1:SQL

C2:UNIX

C3:Java

C0包含了全部员工，起到了“员工主表”的作用（如果原本就提供了这样一张主表，请直接使用它）。C1～C3是每个课程的学习者的集合。这里以C0为主表，依次对C1～C3进行外连接操作。如果某位员工学习过某个课程，则相应的课程列会出现他的姓名，否则为NULL。最后，通过CASE表达式将课程列中员工的姓名转换为○就算完成了。

这次，因为目标表格的表头是3列，所以进行了3次外连接。列数增加时原理也是一样的，只需要增加外连接操作就可以了。想生成置换了表头和表侧栏的交叉表时，我们也可以用同样的思路。这种做法具有比较直观和易于理解的优点，但是因为大量用到了内嵌视图和连接操作，代码会显得很臃肿。而且，随着表头列数的增加，性能也会恶化。

我们再考虑一下有没有更好的做法。一般情况下，外连接都可以用标量子查询替代，因此可以像下面这样写。

这里的要点在于使用标量子查询来生成3列表头。最后一行FROM子句的集合C0和前面的“员工主表”是一样的。标量子查询的条件也和外连接一样，即满足条件时返回○，不满足条件时返回NULL。这种做法的优点在于，需要增加或者减少课程时，只修改SELECT子句即可，代码修改起来比较简单。

例如想加入第4列“PHP入门”时，只需要在SELECT子句的最后加上下面这条语句就可以了（如果采用前面的写法，则必需修改SELECT子句和FROM子句两个地方）。

这种做法不仅利于应对需求变更，对于需要动态生成SQL的系统也是很有好处的。缺点是性能不太好，目前在SELECT子句中使用标量子查询（或者关联子查询）的话，性能开销还是相当大的。

接下来介绍第三种方法，即嵌套使用CASE表达式。CASE表达式可以写在SELECT子句里的聚合函数内部，也可以写在聚合函数外部（请参考CASE表达式文章）。这里，我们先把SUM函数的结果处理成1或者NULL，然后在外层的CASE表达式里将1转换成○。

如果不使用聚合，那么返回结果的行数会是表Courses的行数，所以这里以参加培训课程的员工为单位进行聚合。这种做法和标量子查询的做法一样简洁，也能灵活地应对需求变更。

关于将聚合函数的返回值用于条件判断的写法，如果大家不习惯，可能会有点疑惑。但是，其实在SELECT子句里，聚合函数的执行结果也是标量值，因此可以像常量和普通列一样使用。如果明白这点，就不难理解了。

前面，我们练习了从行转换为列，这回我们反过来，练习一下从列转换为行。我们假设存在下面这样一张让数据库工程师想哭的表。

Personnel：员工子女信息

这种结构的表大家应该都见过吧。将COBOL等语言中使用的平面文件作为输入数据，简单地按照原来的格式进行提取，就可以得到这样的表。这张表到底哪里让人想哭，我们暂时不提，我们需要做的是将这张表转换成行格式的数据。这里使用UNION ALL来实现。

结果：

因为UNION ALL不会排除掉重复的行，所以即使吉田没有孩子，结果里也会出现3行相关数据。把结果存入表时，最好先排除掉“child”列为NULL的行。

不过，根据具体需求，有时需要把没有孩子的吉田也留在表里，像下面这张“员工子女列表”这样。

员工子女列表

在这道例题中，我们不能单纯地将“child”列为NULL的行排除掉。能想到的解法有好几个，不过先来生成一个存储子女列表的视图（孩子主表）吧。

结果：

如果原本就有这样一张员工子女表备用，请直接使用它。那么，接下来我们以员工列表为主表进行外连接操作。请注意连接条件。

这里对子女主表和员工表执行了外连接操作，重点在于连接条件是通过IN谓词指定的。这样一来，当表Personnel里“孩子1～孩子3”列的名字存在于Children视图里时，返回该名字，否则返回NULL。工藤家和铃木家有同名的孩子“夏子”，但这并不影响结果的正确性。

在生成统计表的工作中，经常会有制作嵌套式表头和表侧栏的需求。例如这个例子：表TblPop是一张按照县、年龄层级和性别统计的人口分布表，要求根据表TblPop生成交叉表“包含嵌套式表侧栏的统计表”。

年龄层级主表：TblAge

性别主表：TblSex

人口分布表：TblPop

包含嵌套式表侧栏的统计表

这个问题的要点在于，虽然表TblPop中没有一条年龄层级为2的数据，但是返回结果还是要包含这个年龄层级，固定输出6行。生成固定的表侧栏需要用到外连接，但如果要将表侧栏做成嵌套式的，还需要再花点工夫。目标表的侧栏是年龄层级和性别，所以我们需要使用表TblAge和表TblSex作为主表。

思路是以这两张表作为主表进行外连接操作。但是如果像下面的SQL语句这样简单地进行两次外连接，并不能得到正确的结果。

结果：

观察返回结果可以发现，结果里没有出现年龄层级为2的行。这不是我们想要的。我们已经使用了外连接，为什么结果还是不正确呢？

原因是表TblPop里没有年龄层级为2的数据。也许大家会觉得奇怪，我们已经使用了外连接，而外连接的作用不就是保证在这种情况下也能获取定制化的结果吗？

没错，确实是这样的。实际上，与年龄层级主表外连接之后，结果里是包含年龄层级为2的数据的。

结果：

但是请注意，核心点在这里：虽然年龄层级2确实可以通过外连接从表TblAge获取，但是在表TblPop里，与之相应的“性别编号”列却是NULL。

原因也不难理解。表TblPop里本来就没有年龄层级为2的数据，自然也没有相应的性别信息m或f，于是“性别编号”列只能是NULL。因此与性别主表进行外连接时，连接条件会变成ON MASTER2.sex_cd =NULL，结果是unknown（这个真值的意思请参考三值逻辑一文）。

因此，最终结果里永远不会出现年龄层级为2的数据，即使改变两次外连接的先后顺序，结果也还是一样的。

那么，究竟怎样才能生成正确的嵌套式表侧栏呢？答案如下。

如果不允许进行两次外连接，那么调整成一次就可以了。

结果：

这样，我们就准确无误地得到了6行数据。无论表TblPop里的数据有怎样的缺失，结果的表侧栏总能固定为6行。技巧是对表TblAge和表TblSex进行交叉连接运算，生成下面这样的笛卡儿积。行数是3×2＝6。

MASTER

然后，只需对这张MASTER视图进行一次外连接操作即可。也就是说，需要生成嵌套式表侧栏时，事先按照需要的格式准备好主表就可以了。当需要3层或3层以上的嵌套式表侧栏时，也可以按照这种方法进行扩展。

这里补充一下：对于不支持CROSS JOIN语句的数据库，可以像FROM TblAge, TblSex这样不指定连接条件，把需要连接的表写在一起，其效果与交叉连接一样。

我们之前提到过“在SQL里，交叉连接相当于乘法运算”，这种说法并不是一种比喻，而是事实，观察一下运算前后的行数就能明白这点。

接下来，我们将以下面的商品主表和商品销售历史管理表为例，深入探讨一下。

Items

SalesHistory

先使用这两张表生成一张统计表，以商品为单位汇总出各自的销量。我们期望的结果是像下面这样的。

因为没有销售记录（完全卖不动）的40号商品DVD也需要输出在结果里，所以很显然，这里需要使用外连接。恐怕很多人会想到下面这种做法。

这种做法的确是正确的，代码也很容易理解。这条语句首先在连接前按商品编号对销售记录表进行聚合，进而生成了一张以item_no为主键的临时视图，如下表所示。

以商品编号为主键的临时视图（SH）

接下来，通过“item_no”列对商品主表和这个视图进行连接操作后，商品主表和临时视图就成为了在主键上进行的一对一连接。这个查询看起来还真不错。

但是，如果从性能角度考虑，这条SQL语句还是有些问题的。比如临时视图SH的数据需要临时存储在内存里，还有就是虽然通过聚合将item_no变成了主键，但是SH上却不存在主键索引，因此我们也就无法利用索引优化查询。

要改善这个查询，关键在于导入“把连接看作乘法运算”这种视点。商品主表Items和视图SH确实是一对一的关系，但其实从“item_no”列看，表Items和表SalesHistory是一对多的关系。而且，当连接操作的双方是一对多关系时，结果的行数并不会增加。这就像普通乘法里任意数乘以1后，结果不会变化一样。

外连接会增加只在商品主表里存在的40号商品DVD的行，所以严格地讲，行数并非没有增加。但是这不会导致表SalesHistory里已有的10号或20号商品的行异常增加，进而引起结果异常。按照这种思路改良后的SQL语句如下所示。

这种做法代码更简洁，而且没有使用临时视图，所以性能也会有所改善。

如果表Items里的“items_no”列内存在重复行，就属于多对多连接了，因而这种做法就不能再使用。这时，需要先把某张表聚合一下，使两张表变成一对多的关系。

一对一或一对多关系的两个集合，在进行连接操作后行数不会（异常地）增加。

这个技巧在需要使用连接和聚合来解决问题时非常有用，请熟练掌握。读到这里应该有人注意到了，其实前面那个统计人口分布的问题也可以用类似的方法来解决。大家可以自行尝试一下用上述的方法来解决问题。

本文的前半部分主要从应用的角度介绍了外连接的一些内容。后半部分将换个角度，从面向集合的角度介绍一下外连接本身的一些性质。

标准SQL里定义了外连接的三种类型，如下所示。

●左外连接（LEFT OUTER JOIN）

● 右外连接（RIGHT OUTER JOIN）

● 全外连接（FULL OUTER JOIN）

其中，左外连接和右外连接没有功能上的区别。用作主表的表写在运算符左边时用左外连接，写在运算符右边时用右外连接。相信这两种大家已经很熟悉了。在这三种里，全外连接相对来说使用较少。从面向集合的角度来看，它有很多有趣的特点，所以接下来我们主要了解一下全外连接。

关于“全外连接到底是怎么回事”，相比用语言来描述，具体的实例更容易让我们明白，所以这里先来看一个简单的例子。

Class_A

Class_B

在这两张班级学生表里，田中和铃木同时属于两张表，而伊集院和西园寺只属于其中一张表。全外连接是能够从这样两张内容不一致的表里，没有遗漏地获取全部信息的方法，所以也可以理解成“把两张表都当作主表来使用”的连接。

结果：

可以看到，两张表里的4个人全部出现在结果里了。COALESCE是SQL的标准函数，可以接受多个参数，功能是返回第一个非NULL的参数。使用左（右）外连接时，只能使用两张表中的一张作为主表，所以不能同时获取到伊集院和西园寺两个人。而全外连接的“全”就是“保留全部信息”的意思。

如果所用的数据库不支持全外连接，可以分别进行左外连接和右外连接，再把两个结果通过UNION合并起来，也能达到同样的目的。

这种写法虽然也能获取到同样的结果，但是代码比较冗长，而且使用两次连接后还要用UNION来合并，性能也不是很好。

其实，我们还可以换个角度，把表连接看成集合运算。内连接相当于求集合的积（INTERSECT，也称交集），全外连接相当于求集合的和（UNION，也称并集）。下面是两者的维恩图（Venn Diagram，亦称文氏图）。

内连接相当于求集合的积（INTERSECT）

全外连接相当于求集合的和（UNION）

在“不遗漏任何信息”这一点上，UNION和全外连接非常相似（MERGE语句也是）。接下来，我们将利用外连接的这些特性，实际练习一下集合运算。

SQL是以集合论为基础的，但令人费解的是，很长一段时间内它连基础的集合运算都不支持。UNION是SQL-86标准开始加入的，还算比较早。INTERSECT和EXCEPT都是SQL-92标准才加入的。关系除法运算还没有被标准化。

而且，各个DBMS提供商在功能的实现程度上也有所不同，参差不齐。集合运算符会进行排序，所以可能会带来性能上的问题。因此，了解一下集合运算符的替代方案还是有意义的。

前面介绍了交集和并集，下面来介绍一下差集的求法。注意一下前面有关全外连接的例子会发现，伊集院在A班里存在而在B班里不存在，“B_name”列的值是NULL；相反，西园寺在B班里存在而在A班里不存在，“A_name”列的值是NULL。于是，我们可以通过判断连接后的相关字段是否为NULL来求得差集。

用外连接求差集：A－B

结果：

用外连接求差集（A-B）

用外连接求差集：B－A

结果:

用外连接求差集（B-A）

当然，用外连接解决这个问题不太符合外连接原本的设计目的。但是对于不支持差集运算的数据库来说，这也可以作为NOT IN和NOT EXISTS之外的另一种解法，而且它可能是差集运算中效率最高的，这也是它的优点。

用全外连接求异或集

接下来我们考虑一下如何求两个集合的异或集。SQL没有定义求异或集的运算符，如果用集合运算符，可以有两种方法。一种是(A UNION B)EXCEPT (A INTERSECT B)，另一种是(A EXCEPT B) UNION (B EXCEPT A)。两种方法都比较麻烦，性能开销也会增大。

现在请再仔细看一下前面有关全外连接的执行结果，是否得到了灵感呢？

结果

用全外连接求异或集

像这样改变一下WHERE子句的条件，就可以进行各种集合运算。现在我们已经求了集合的并集、差集、交集，那么想求集合的商时该怎么做呢？其实商也可以通过外连接来求。也就是说，我们在HAVING子句一文里介绍过的关系除法运算也可以通过外连接来实现。如果使用HAVING子句一文里的表Items和表ShopItems，可以像下面这样写。

结果

这个查询的意思是，用表Items减去表ShopItems里各个店铺的商品，如果结果是空集，则说明该店铺库存里有表Items里的全部商品。

其中，“各个店铺”这一条件是通过ON子句里的SI1.shop = SI2.shop这个关联子查询来表述的。因为这种解法用到了集合的差集，所以也可以直接使用EXCEPT运算符来实现。大家可以尝试按照这种思路改写一下。

本篇文章我们稍微了解一点与外连接写法相关的内容。SQL有很多的方言，例如外连接，Oracle数据库使用“(+)”，而SQL Server数据库使用“＊=”等，非常依赖于数据库的具体实现。从代码的可移植性来说，我们应该避免采用这样独特的写法，并遵循ANSI标准。因此，我们统一采用了标准的写法。

以上就是今天分享的全部内容，整理了一份数据分析资料，评论区领取。

想学习数据分析的朋友关注下我，整理不易，点点赞，拜托了！